﻿using System;
using System.Data;
using System.Data.SqlClient;

namespace RCSP.DataAccess
{
    public class MemberDataAccess : RCSPDatabaseAccess
    {
        public MemberDataAccess()
        {
        }

        public MemberDataAccess(string connectionString)
            : base(connectionString)
        {
        }

        public DataSet GetMemberById(string memberId)
        {
            var command = new SqlCommand
            {
                Connection = Connection,
                CommandType = CommandType.StoredProcedure,
                CommandText = "GetMemberById"
            };

            command.Parameters.Add(new SqlParameter("@accountNumber", memberId));
            Connection.Open();
            var adapter = new SqlDataAdapter(command);
            var ds = new DataSet();
            adapter.Fill(ds, "Member");

            return ds;
        }

        public int SaveMemberToDB(string storeProcedure,string accountNumber, string kanaName, string name, string genderClassification,
            DateTime? dateOfBirth, string zip, string address1, string address2, string phoneNumber
            , int? DateOfBirthYear, int? dateOfBirthMonth, int? dateOfBirthDay
            , string areaCode, string billingCode, string billingClassification
            , string paymentMethod, string paymentDivision, int? yearBillingMonth, int? numberOfUnits, int? bankCode
            , int? branchCode, string depositEvent, string account, string accountName, string parentMembershipNumber
            , string workPostalCode, string workAddress2, string workPhoneNumber, string memberClassification
            , int? dMDestination, DateTime? withdrawalDate, DateTime? changeProcessDate, string theReasonForStoppingUse
            , string hospital, string contact, string billingPostalCode, string billingDepartmentJobTitle,
            string photoFileName, DateTime? photoDate)
        {
            var command = new SqlCommand();
            command.Connection = Connection;
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = storeProcedure;
            command.Parameters.Add(new SqlParameter("@accountNumber", accountNumber));
            command.Parameters.Add(new SqlParameter("@KanaName", kanaName));
            command.Parameters.Add(new SqlParameter("@Name", name));
            command.Parameters.Add(new SqlParameter("@GenderClassification", genderClassification));
            command.Parameters.Add(SetParam("@DateOfBirth", dateOfBirth));
            command.Parameters.Add(new SqlParameter("@Zip", zip));
            command.Parameters.Add(new SqlParameter("@Address1", address1));
            command.Parameters.Add(new SqlParameter("@Address2", address2));
            command.Parameters.Add(new SqlParameter("@PhoneNumber", phoneNumber));
            command.Parameters.Add(SetParam("@DateOfBirthYear", DateOfBirthYear));
            command.Parameters.Add(SetParam("@DateOfBirthMonth", dateOfBirthMonth));
            command.Parameters.Add(SetParam("@DateOfBirthDay", dateOfBirthDay));
            command.Parameters.Add(new SqlParameter("@AreaCode", areaCode));
            command.Parameters.Add(new SqlParameter("@BillingCode", billingCode));
            command.Parameters.Add(new SqlParameter("@BillingClassification", billingClassification));
            command.Parameters.Add(new SqlParameter("@PaymentMethod", paymentMethod));
            command.Parameters.Add(new SqlParameter("@PaymentDivision", paymentDivision));
            command.Parameters.Add(SetParam("@YearBillingMonth", yearBillingMonth));
            command.Parameters.Add(new SqlParameter("@NumberOfUnits", numberOfUnits));
            command.Parameters.Add(SetParam("@BankCode", bankCode));
            command.Parameters.Add(SetParam("@BranchCode", branchCode));
            command.Parameters.Add(new SqlParameter("@DepositEvent", depositEvent));
            command.Parameters.Add(new SqlParameter("@Account", account));
            command.Parameters.Add(new SqlParameter("@AccountName", accountName));
            command.Parameters.Add(new SqlParameter("@ParentMembershipNumber", parentMembershipNumber));
            command.Parameters.Add(new SqlParameter("@WorkPostalCode", workPostalCode));
            command.Parameters.Add(new SqlParameter("@WorkAddress2", workAddress2));
            command.Parameters.Add(new SqlParameter("@WorkPhoneNumber", workPhoneNumber));
            command.Parameters.Add(new SqlParameter("@MemberClassification", memberClassification));
            command.Parameters.Add(SetParam("@DMDestination", dMDestination));
            command.Parameters.Add(SetParam("@WithdrawalDate", withdrawalDate));
            command.Parameters.Add(SetParam("@ChangeProcessDate", changeProcessDate));
            command.Parameters.Add(new SqlParameter("@TheReasonForStoppingUse", theReasonForStoppingUse));
            command.Parameters.Add(new SqlParameter("@Hospital", hospital));
            command.Parameters.Add(new SqlParameter("@Contact", contact));
            command.Parameters.Add(new SqlParameter("@BillingPostalCode", billingPostalCode));
            command.Parameters.Add(new SqlParameter("@BillingDepartmentJobTitle", billingDepartmentJobTitle));
            command.Parameters.Add(new SqlParameter("@PhotoFileName", photoFileName));
            command.Parameters.Add(SetParam("@PhotoDate", photoDate));
            Connection.Open();
            return command.ExecuteNonQuery();
            Connection.Close();
        }

        public void AddNewPersonalInformation(string accountNumber, string kanaName, string name, string genderClassification,
            DateTime? dateOfBirth, string zip, string address1, string address2, string phoneNumber
            , int? DateOfBirthYear, int? dateOfBirthMonth, int? dateOfBirthDay
            , string areaCode) 
        {
            var command = new SqlCommand();
            command.Connection = Connection;
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "Insert_T個人情報_MemberInformation";
            command.Parameters.Add(new SqlParameter("@accountNumber", accountNumber));
            command.Parameters.Add(new SqlParameter("@KanaName", kanaName));
            command.Parameters.Add(new SqlParameter("@Name", name));
            command.Parameters.Add(new SqlParameter("@GenderClassification", genderClassification));
            command.Parameters.Add(SetParam("@DateOfBirth", dateOfBirth));
            command.Parameters.Add(new SqlParameter("@Zip", zip));
            command.Parameters.Add(new SqlParameter("@Address1", address1));
            command.Parameters.Add(new SqlParameter("@Address2", address2));
            command.Parameters.Add(new SqlParameter("@PhoneNumber", phoneNumber));
            command.Parameters.Add(SetParam("@DateOfBirthYear", DateOfBirthYear));
            command.Parameters.Add(SetParam("@DateOfBirthMonth", dateOfBirthMonth));
            command.Parameters.Add(SetParam("@DateOfBirthDay", dateOfBirthDay));
            command.Parameters.Add(new SqlParameter("@AreaCode", areaCode));
            Connection.Open();
            command.ExecuteNonQuery();
            Connection.Close();
        }



        private SqlParameter SetParam(string paramName, object value)
        {
            return value == null ? new SqlParameter(paramName, DBNull.Value) : new SqlParameter(paramName, value);
        }
    }
}